/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package DataAccessObject;

import EntityObject.tblQuanHeEO;
import EntityObject.tblThanhVienEO;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author minh
 */
public class tblThanhVienDAO {
   
    public static boolean ThanhVien_Insert(tblThanhVienEO _tblThanhVienEO,  tblQuanHeEO _tblQuanHeEO) throws SQLException{
        try {
            String sql="{call tblThanhVien_Insert(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
            CallableStatement cstmt=Connection.conn().prepareCall(sql);
            cstmt.setString(1, _tblThanhVienEO.getsTenTV());
            cstmt.setString(2, _tblThanhVienEO.getsTenHuy());
            cstmt.setBoolean(3, _tblThanhVienEO.getbGioiTinh());
            cstmt.setString(4, _tblThanhVienEO.getsNoiSinh());
            cstmt.setString(5, _tblThanhVienEO.getsNoiOHienTai());
            cstmt.setString(6, _tblThanhVienEO.getsNgaySinh());
            cstmt.setString(7, _tblThanhVienEO.getsNgayMat());
            cstmt.setString(8, _tblThanhVienEO.getsNoiAnTang());
            cstmt.setString(9, _tblThanhVienEO.getsTrinhDoVH());
            cstmt.setString(10, _tblThanhVienEO.getsNgheNghiep());            
            cstmt.setInt(11, _tblThanhVienEO.getiDoiThu());
            cstmt.setString(12, _tblThanhVienEO.getsNhomMau());
            cstmt.setString(13, _tblThanhVienEO.getsGhiChu());
            cstmt.setInt(14, _tblQuanHeEO.getFk_MaTV_Bo());
            cstmt.setInt(15, _tblQuanHeEO.getFK_MaTV_Me());
            cstmt.execute();
            return true;
        }
        catch (SQLException e) {
            System.out.println(e);
            return false;
        }
        finally{Connection.conn().close();}
    }
    
    public static boolean ThanhVien_Update(tblThanhVienEO _tblThanhVienEO,  tblQuanHeEO _tblQuanHeEO) throws SQLException{
        try {
            
            String sql="{call tblThanhVien_Update(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
            CallableStatement cstmt=Connection.conn().prepareCall(sql);
            cstmt.setInt(1, _tblThanhVienEO.getPK_iMaTV());
            cstmt.setString(2, _tblThanhVienEO.getsTenTV());
            cstmt.setString(3, _tblThanhVienEO.getsTenHuy());
            cstmt.setBoolean(4, _tblThanhVienEO.getbGioiTinh());
            cstmt.setString(5, _tblThanhVienEO.getsNoiSinh());
            cstmt.setString(6, _tblThanhVienEO.getsNoiOHienTai());
            cstmt.setString(7, _tblThanhVienEO.getsNgaySinh());
            cstmt.setString(8, _tblThanhVienEO.getsNgayMat());
            cstmt.setString(9, _tblThanhVienEO.getsNoiAnTang());
            cstmt.setString(10, _tblThanhVienEO.getsTrinhDoVH());
            cstmt.setString(11, _tblThanhVienEO.getsNgheNghiep());            
            cstmt.setInt(12, _tblThanhVienEO.getiDoiThu());
            cstmt.setString(13, _tblThanhVienEO.getsNhomMau());
            cstmt.setString(14, _tblThanhVienEO.getsGhiChu());            
            cstmt.setInt(15, _tblQuanHeEO.getFk_MaTV_Bo());
            cstmt.setInt(16, _tblQuanHeEO.getFK_MaTV_Me());
                        
            cstmt.execute();
            return true;
        }
        catch (SQLException e) {
            System.out.println(e);
            return false;
        }
        finally{Connection.conn().close();}
    }
    
    public static boolean ThanhVien_Delete(tblThanhVienEO _tblThanhVienEO) throws SQLException{
        try {
            String sql="{call tblThanhVien_Delete(?)}";
            CallableStatement cstmt=Connection.conn().prepareCall(sql);
            cstmt.setInt(1, _tblThanhVienEO.getPK_iMaTV());
            cstmt.execute();
            return true;
        }
        catch (SQLException e) {
            System.out.println(e);
            return false;
        }
        finally{Connection.conn().close();}
    }
    
    public static ResultSet ThanhVien_SelectList(tblThanhVienEO _tblThanhVienEO) throws SQLException{
        ResultSet rs =null;
        try {
            String sql="{call tblThanhVien_SelectList()}";
            CallableStatement cstmt=Connection.conn().prepareCall(sql);
//            cstmt.setString(1, dungcu.getMaDungCu());
            rs=cstmt.executeQuery();
            return rs;
        }
        catch (SQLException e) {
            System.out.println(e);
            return rs;
        }
        finally{Connection.conn().close();}
    }
    
    public static ResultSet ThanhVien_SelectItem(tblThanhVienEO _tblThanhVienEO) throws SQLException{
        ResultSet rs =null;
        try {
            String sql="{call tblThanhVien_SelectItem(?)}";
            CallableStatement cstmt=Connection.conn().prepareCall(sql);
            cstmt.setInt(1, _tblThanhVienEO.getPK_iMaTV());
            rs=cstmt.executeQuery();
            return rs;
        }
        catch (SQLException e) {
            System.out.println(e);
            return rs;
        }
        finally{Connection.conn().close();}
    } 
    
    public static ResultSet ThanhVien_SelectItemByID(int PK_iMaTV) throws SQLException{
        ResultSet rs =null;
        try {
            String sql="{call tblThanhVien_SelectItem(?)}";
            CallableStatement cstmt=Connection.conn().prepareCall(sql);
            cstmt.setInt(1, PK_iMaTV);
            rs=cstmt.executeQuery();
            return rs;
        }
        catch (SQLException e) {
            System.out.println(e);
            return rs;
        }
        finally{Connection.conn().close();}
    } 
    
    
    public static ResultSet ThanhVien_SelectList_KetXuat(tblThanhVienEO _tblThanhVienEO,int tuoi) throws SQLException{
        ResultSet rs =null;
        try {
            String sql="{call tblThanhVien_SelectList_KetXuat(?,?,?,?)}";
            CallableStatement cstmt=Connection.conn().prepareCall(sql);
            cstmt.setString(1, _tblThanhVienEO.getsTenTV());
            cstmt.setInt(2, _tblThanhVienEO.getiDoiThu());
            if(_tblThanhVienEO.getbGioiTinh() !=null){
                cstmt.setBoolean(3, _tblThanhVienEO.getbGioiTinh());
            }
            else{
                cstmt.setString(3, null);
            }
            cstmt.setInt(4, tuoi);
            rs=cstmt.executeQuery();
            return rs;
        }
        catch (SQLException e) {
            System.out.println(e);
            return rs;
        }
        finally{Connection.conn().close();}
    }
}
